2/25/2008 – Paging result sets in SQL Server 2005

Well, between watching college basketball and teaching and writing my next CoDe article, I've ignored my blog over the last month.

I have a community presentation that I've done over the last year and a half – it covers ASP.NET AJAX and SQL 2005. It's based on an actual client application that runs against a database with about a million rows.

Here's the "end result" . This is a bare-boned version, mainly intended to demonstrate fundamentals The client version, which I'm not allowed to show, is much more "pimped out" J

The presentation focuses on building a stored procedure in SQL 2005 that can handle the following:

• Searching a large database, based on full or partial text searches on Name, Address, City, etc.
• Searching on a variable # of statuses
• Paging the result set, and allowing the user to navigate back and forth (first ten, next ten, previous ten, last ten)
• Allowing user to specify the sort
• Allowing the user to quickly navigate to a letter, based on the current sort. (For instance, sorting on City and allowing someone to jump to the 'R's). If you think this isn't important – consider most web email clients…how many times have you had to click NEXT….NEXT…NEXT…NEXT to get to a certain area of the results???)
• Using ASP.NET AJAX, to implement partial page refreshes

OK, seems pretty simple? Well, it's amazing how a seemingly simple module can contain many fundamentals!

I've been in a debate with others about use of stored procedures for a task like this. I advocate stored procs as a starting point, unless there are critical reasons not to. Given that there are a good handful of variables here, some will choose the path of dynamic SQL, of constructing a SQL expression. Well, that's fine – however, it CAN be done with a stored procedure. Through the use of CASE statements, COALESCE statements to deal with NULL parameters, one can build a stored proc with the same flexibility. Bottom line: where possible, I'll always opt for creating a database API for tasks, and set up a basic test environment (which I'll show further down).

So here's the stored procedure. It receives the text search parameters, along with a startrowIndex (that's managed by the web application), the sort column to be used, as well as an optional "jump-to" character.

Also note there's an XML parameter, for the list of status codes. Sure, this particular situation only has a handful of status codes….but you could have other instances of variable selections where the list might be much larger (say a user selects 20 customers, or an even greater number of products). SQL 2005's XML capabilities are perfect for something like this – you can pass an XML string as a parameter, and shred it into a table variable back in SQL Server. (I've included a reusable UDF for this at the end, it's called XMLToTable, which you'll see used in the following stored proc):

ALTER PROCEDURE [dbo].[LookupEmployeeData]
    @LastName varchar(50)=null, @FirstName varchar(50)=null,
    @Address varchar(50)=null, @City varchar(50)=null,
    @State varchar(2)=null, @Zip varchar(50)=null,
    @StartRowIndex int, @MaxRows int,
    @AlphaChar varchar(1)=null, @SortCol varchar(20)=null
    @XMLStatuses XML
AS
BEGIN
-- StartRowIndex represents the first row to grab, based on current sort
-- MaxRows represents the max # of rows to return

-- alphaChar represents a "jump-to" character, based on current sort
-- (if an alphaChar is specified,
-- then the StartRowIndex is ignored

-- So someone might be sorting on City, and wants to jump straight to the 'R' cities
-- JumpChar is to give user an easier way than click NEXT…NEXT…NEXT…NEXT

-- All search parms are optional

SET NOCOUNT ON
DECLARE @lJumpToChar bit
-- If user did not specify a "jump-to" alphanumeric char, then
-- we're doing straight paging, based on the startrow Index
IF @AlphaChar is null
    SET @lJumpToChar = 0
ELSE
    -- If user DID specify a "jump-to" alphanumeric char, then
    -- we're going straight to the first occurance of the alphachar,
    -- based on the current sort
    SET @lJumpToChar = 1

   
-- Query into CTE for 'base' results, and assign a row #, based on the ORDER
WITH CustListTemp AS
    (SELECT CustomerID, LastName, FirstName, Address, City, State,
        Zip, ROW_NUMBER() OVER (ORDER BY
            CASE @SortCol
                WHEN 'LASTNAME' THEN LastName + Firstname
                WHEN 'ADDRESS' THEN Address
                WHEN 'CITY' THEN City + LastName + Firstname
                WHEN 'STATE' THEN STATE + LastName + Firstname
                WHEN 'ZIP' THEN ZIP + LastName + Firstname
                ELSE LastName + Firstname END)
            AS RowNum
    FROM Customers
    -- Use the Table-valued UDF XMLToTable to shred an XML string
    -- into a table variable, for querying against a set of values
    JOIN dbo.XMLToTable(@XMLStatuses) StatList
        ON StatList.IntPk = Customers.StatusFK
        -- Check all the text searches…use COALESCE (or ISNULL), since some
        -- could be NULL
        WHERE LastName LIKE '%' + COALESCE(@LastName,LastName)+ '%' AND
            FirstName LIKE '%' + COALESCE(@FirstName,FirstName) + '%' AND
            Address LIKE '%' + COALESCE(@Address,Address) + '%' AND
            City LIKE '%' + COALESCE(@City,City) + '%' AND
            State LIKE '%' + COALESCE(@State,State) + '%' AND
            Zip LIKE '%' + COALESCE(@Zip,Zip) + '%' )

    -- Now query the CTE, for the TOP (N) rows,
    -- based on either row number position (paging)
    -- or the JumpChar
    SELECT TOP (@MaxRows) CustomerID, LastName, FirstName,
                Address, City, State, Zip, RowNum
    FROM
        ( SELECT CustListTemp.*,
        (SELECT COUNT(*) from CustListTemp) AS RecCount FROM CustListTemp ) CustList
        WHERE
            CASE
                -- If the JumpChar is true, check the first character
                -- of the sort order column against the AlphaChar
                WHEN @lJumpToChar = 1 AND @SortCol= 'LASTNAME' AND
                    SUBSTRING(LastName,1,1) >= RTRIM(@AlphaChar) THEN 1
                WHEN @lJumpToChar = 1 AND @SortCol= 'ADDRESS' AND
                    SUBSTRING(Address,1,1) >= RTRIM(@AlphaChar) THEN 1
                WHEN @lJumpToChar = 1 AND @SortCol= 'CITY' AND
                    SUBSTRING(City,1,1) >= RTRIM(@AlphaChar) THEN 1
                WHEN @lJumpToChar = 1 AND @SortCol= 'STATE' AND
                    SUBSTRING(State,1,1) >= RTRIM(@AlphaChar) THEN 1
                WHEN @lJumpToChar = 1 AND @SortCol= 'ZIP' AND
                    SUBSTRING(Zip,1,1) >= RTRIM(@AlphaChar) THEN 1

                -- If JumpChar is false, then check the row number
                -- for the valid range

                -- Note that if StartRowIndex is -1, that means user
                -- wants the last page…in that case, count backwards from MaxRows
                WHEN @lJumpToChar = 0 AND RowNum BETWEEN
                    (CASE @StartRowIndex
                        WHEN -1 THEN ( RecCount ) - @MaxRows
                        ELSE @StartRowIndex
                    END )
                    AND
                        ( CASE @StartRowIndex
                            WHEN -1 then ( RecCount )- @MaxRows
                        ELSE @StartRowIndex
                        END) + @MaxRows
                    THEN 1
                ELSE 0 END = 1
END
GO

And I promised the code for the reusable Table-valued UDF, to "shred" an XML string into a table variable (for subsequent joining). It uses the new XQUERY capabilities in 2005 to read nodes from an XML string. With this, I can handle any situation where a user passes a variable number of selections. Here it is:

ALTER FUNCTION [dbo].[XMLtoTable]
    (@XMLString XML )
RETURNS
@tPKList TABLE ( IntPK int )
AS
BEGIN
    INSERT INTO @tPKList
        SELECT Tbl.col.value('.','int') as IntPK
            FROM @XMLString.nodes('//IDpk' ) Tbl(col)
    RETURN
END

So, that's the stored procedure. Now, I always like to test my stored proc inside SQL Management Studio – here's such a test.

So, that's it. Most people who have seen this, and/or have attended the presentation, like the idea. I know that some folks have taken the code and have implemented it for their own situations. Of course, some who eschew stored procedures will think I'm smoking crack. Hey, that's fine.

KG